﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace HRMService
{
    // NOTE: If you change the class name "Detail" here, you must also update the reference to "Detail" in App.config.
    public partial class MainService : IDetail
    {
        public int Detail_Using(int DetailID)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Using", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Detail_ID", DetailID);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Detail_GetAll()
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Detail_GetList(out int ItemCount, int currentPage, int pageSize, int _DocumentID)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_GetList", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@CurrentPage", currentPage);
            dbCmd.Parameters.AddWithValue("@PageSize", pageSize);
            dbCmd.Parameters.AddWithValue("@DocumentID", _DocumentID);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
                ItemCount = Convert.ToInt32(retVal.Tables[1].Rows[0][0]);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_GetBySIBookNo(string _SIBookNo, string _BranchCode, DateTime _Dateval)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_GetBySIBookNo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@InsBookNo", _SIBookNo);
            dbCmd.Parameters.AddWithValue("@BranchCode", _BranchCode);
            if (_Dateval == DateTime.MinValue)
                dbCmd.Parameters.AddWithValue("@DateVal", DBNull.Value);
            else
                dbCmd.Parameters.AddWithValue("@DateVal", _Dateval);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DetailInfo Detail_GetInfo(int DetailID)
        {
            DetailInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Detail_ID", DetailID);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new DetailInfo();
                    retVal.ID = Convert.ToInt32(dr["Detail_ID"]);
                    retVal.DocumentID = Convert.ToInt32(dr["Detail_DocumentID"]);
                    retVal.EmployeeCode = dr["Detail_EmployeeCode"].ToString();
                    retVal.InsBookNo = dr["Detail_InsBookNo"].ToString();
                    retVal.FromDate = Convert.ToDateTime(dr["Detail_FromDate"]);
                    retVal.ToDate = Convert.ToDateTime(dr["Detail_ToDate"]);
                    retVal.InsTypeCode = Convert.ToString(dr["Detail_InsTypeCode"]);
                    retVal.SocialRate = Convert.ToDecimal(dr["Detail_SocialRate"]);
                    retVal.MedicalRate = Convert.ToDecimal(dr["Detail_MedicalRate"]);
                    retVal.UnemployedRate = Convert.ToDecimal(dr["Detail_UnemployedRate"]);
                    retVal.NewBasicFactor = Convert.ToDecimal(dr["Detail_NewBasicFactor"]);
                    retVal.NewPositionFactor = Convert.ToDecimal(dr["Detail_NewPositionFactor"]);
                    retVal.NewOverFactor = Convert.ToDecimal(dr["Detail_NewOverFactor"]);
                    retVal.NewJobFactor = Convert.ToDecimal(dr["Detail_NewJobFactor"]);
                    retVal.NewAreaFactor = Convert.ToDecimal(dr["Detail_NewAreaFactor"]);
                    retVal.OldBasicFactor = Convert.ToDecimal(dr["Detail_OldBasicFactor"]);
                    retVal.OldPositionFactor = Convert.ToDecimal(dr["Detail_OldPositionFactor"]);
                    retVal.OldOverFactor = Convert.ToDecimal(dr["Detail_OldOverFactor"]);
                    retVal.OldJobFactor = Convert.ToDecimal(dr["Detail_OldJobFactor"]);
                    retVal.OldAreaFactor = Convert.ToDecimal(dr["Detail_OldAreaFactor"]);
                    retVal.NewAmount = Convert.ToDecimal(dr["Detail_NewAmount"]);
                    retVal.OldAmount = Convert.ToDecimal(dr["Detail_OldAmount"]);
                    retVal.RegionCode = Convert.ToString(dr["Detail_RegionCode"]);
                    retVal.HospitalCode = Convert.ToString(dr["Detail_HospitalCode"]);
                    retVal.Type = Convert.ToString(dr["Detail_Type"]);
                    retVal.TakeCard = Convert.ToBoolean(dr["Detail_TakeCard"]);
                    retVal.Status = Convert.ToInt32(dr["Detail_Status"]);
                    retVal.TotalMonth = Convert.ToInt32(dr["Detail_TotalMonth"]);
                    retVal.Description = dr["Detail_Description"] != DBNull.Value ? dr["Detail_Description"].ToString() : "";
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
        public DetailInfo Detail_CheckValid(string _InsBookNo, string InsType, string _BranchCode, DateTime _DateVal)
        {
            DetailInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_CheckValid", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@InsBookNo", _InsBookNo);
            dbCmd.Parameters.AddWithValue("@InsTypeCode", InsType);
            dbCmd.Parameters.AddWithValue("@BranchCode", _BranchCode);
            dbCmd.Parameters.AddWithValue("@DateVal", _DateVal);
            SqlDataReader dr = null;
            try
            {
                dbConn.Open();
                dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new DetailInfo();
                    retVal.ID = Convert.ToInt32(dr["Detail_ID"]);
                    retVal.DocumentID = Convert.ToInt32(dr["Detail_DocumentID"]);
                    retVal.EmployeeCode = dr["Detail_EmployeeCode"].ToString();
                    retVal.InsBookNo = dr["Detail_InsBookNo"].ToString();
                    retVal.FromDate = Convert.ToDateTime(dr["Detail_FromDate"]);
                    retVal.ToDate = Convert.ToDateTime(dr["Detail_ToDate"]);
                    retVal.InsTypeCode = Convert.ToString(dr["Detail_InsTypeCode"]);
                    retVal.SocialRate = Convert.ToDecimal(dr["Detail_SocialRate"]);
                    retVal.MedicalRate = Convert.ToDecimal(dr["Detail_MedicalRate"]);
                    retVal.UnemployedRate = Convert.ToDecimal(dr["Detail_UnemployedRate"]);
                    retVal.NewBasicFactor = Convert.ToDecimal(dr["Detail_NewBasicFactor"]);
                    retVal.NewPositionFactor = Convert.ToDecimal(dr["Detail_NewPositionFactor"]);
                    retVal.NewOverFactor = Convert.ToDecimal(dr["Detail_NewOverFactor"]);
                    retVal.NewJobFactor = Convert.ToDecimal(dr["Detail_NewJobFactor"]);
                    retVal.NewAreaFactor = Convert.ToDecimal(dr["Detail_NewAreaFactor"]);
                    retVal.OldBasicFactor = Convert.ToDecimal(dr["Detail_OldBasicFactor"]);
                    retVal.OldPositionFactor = Convert.ToDecimal(dr["Detail_OldPositionFactor"]);
                    retVal.OldOverFactor = Convert.ToDecimal(dr["Detail_OldOverFactor"]);
                    retVal.OldJobFactor = Convert.ToDecimal(dr["Detail_OldJobFactor"]);
                    retVal.OldAreaFactor = Convert.ToDecimal(dr["Detail_OldAreaFactor"]);
                    retVal.NewAmount = Convert.ToDecimal(dr["Detail_NewAmount"]);
                    retVal.OldAmount = Convert.ToDecimal(dr["Detail_OldAmount"]);
                    retVal.RegionCode = Convert.ToString(dr["Detail_RegionCode"]);
                    retVal.HospitalCode = Convert.ToString(dr["Detail_HospitalCode"]);
                    retVal.Type = Convert.ToString(dr["Detail_Type"]);
                    retVal.TakeCard = Convert.ToBoolean(dr["Detail_TakeCard"]);
                    retVal.Status = Convert.ToInt32(dr["Detail_Status"]);
                    retVal.TotalMonth = Convert.ToInt32(dr["Detail_TotalMonth"]);
                    retVal.Description = dr["Detail_Description"] != DBNull.Value ? dr["Detail_Description"].ToString() : "";
                }
            }
            finally
            {
                if (dr != null) dr.Close();
                dbConn.Close();
            }
            return retVal;
        }
        public int Detail_Insert(DetailInfo eInfo)
        {
            int retVal = 0;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Detail_DocumentID", eInfo.DocumentID);
            dbCmd.Parameters.AddWithValue("@Detail_EmployeeCode", eInfo.EmployeeCode);
            dbCmd.Parameters.AddWithValue("@Detail_InsBookNo", eInfo.InsBookNo);
            dbCmd.Parameters.AddWithValue("@Detail_FromDate", eInfo.FromDate);
            dbCmd.Parameters.AddWithValue("@Detail_ToDate", eInfo.ToDate);
            dbCmd.Parameters.AddWithValue("@Detail_InsTypeCode", eInfo.InsTypeCode);
            dbCmd.Parameters.AddWithValue("@Detail_SocialRate", eInfo.SocialRate);
            dbCmd.Parameters.AddWithValue("@Detail_MedicalRate", eInfo.MedicalRate);
            dbCmd.Parameters.AddWithValue("@Detail_UnemployedRate", eInfo.UnemployedRate);
            dbCmd.Parameters.AddWithValue("@Detail_NewBasicFactor", eInfo.NewBasicFactor);
            dbCmd.Parameters.AddWithValue("@Detail_NewPositionFactor", eInfo.NewPositionFactor);
            dbCmd.Parameters.AddWithValue("@Detail_NewOverFactor", eInfo.NewOverFactor);
            dbCmd.Parameters.AddWithValue("@Detail_NewJobFactor", eInfo.NewJobFactor);
            dbCmd.Parameters.AddWithValue("@Detail_NewAreaFactor", eInfo.NewAreaFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldBasicFactor", eInfo.OldBasicFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldPositionFactor", eInfo.OldPositionFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldOverFactor", eInfo.OldOverFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldJobFactor", eInfo.OldJobFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldAreaFactor", eInfo.OldAreaFactor);
            dbCmd.Parameters.AddWithValue("@Detail_RegionCode", eInfo.RegionCode);
            dbCmd.Parameters.AddWithValue("@Detail_HospitalCode", eInfo.HospitalCode);
            dbCmd.Parameters.AddWithValue("@Detail_NewAmount", eInfo.NewAmount);
            dbCmd.Parameters.AddWithValue("@Detail_OldAmount", eInfo.OldAmount);
            dbCmd.Parameters.AddWithValue("@Detail_Type", eInfo.Type);
            dbCmd.Parameters.AddWithValue("@Detail_TakeCard", eInfo.TakeCard);
            dbCmd.Parameters.AddWithValue("@Detail_Status", eInfo.Status);
            dbCmd.Parameters.AddWithValue("@Detail_TotalMonth", eInfo.TotalMonth);
            dbCmd.Parameters.AddWithValue("@Detail_Description", eInfo.Description);
            try
            {
                dbConn.Open();
                retVal = Convert.ToInt32(dbCmd.ExecuteScalar());
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Detail_Update(DetailInfo eInfo)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Detail_ID", eInfo.ID);
            dbCmd.Parameters.AddWithValue("@Detail_DocumentID", eInfo.DocumentID);
            dbCmd.Parameters.AddWithValue("@Detail_EmployeeCode", eInfo.EmployeeCode);
            dbCmd.Parameters.AddWithValue("@Detail_InsBookNo", eInfo.InsBookNo);
            dbCmd.Parameters.AddWithValue("@Detail_FromDate", eInfo.FromDate);
            dbCmd.Parameters.AddWithValue("@Detail_ToDate", eInfo.ToDate);
            dbCmd.Parameters.AddWithValue("@Detail_InsTypeCode", eInfo.InsTypeCode);
            dbCmd.Parameters.AddWithValue("@Detail_SocialRate", eInfo.SocialRate);
            dbCmd.Parameters.AddWithValue("@Detail_MedicalRate", eInfo.MedicalRate);
            dbCmd.Parameters.AddWithValue("@Detail_UnemployedRate", eInfo.UnemployedRate);
            dbCmd.Parameters.AddWithValue("@Detail_NewBasicFactor", eInfo.NewBasicFactor);
            dbCmd.Parameters.AddWithValue("@Detail_NewPositionFactor", eInfo.NewPositionFactor);
            dbCmd.Parameters.AddWithValue("@Detail_NewOverFactor", eInfo.NewOverFactor);
            dbCmd.Parameters.AddWithValue("@Detail_NewJobFactor", eInfo.NewJobFactor);
            dbCmd.Parameters.AddWithValue("@Detail_NewAreaFactor", eInfo.NewAreaFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldBasicFactor", eInfo.OldBasicFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldPositionFactor", eInfo.OldPositionFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldOverFactor", eInfo.OldOverFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldJobFactor", eInfo.OldJobFactor);
            dbCmd.Parameters.AddWithValue("@Detail_OldAreaFactor", eInfo.OldAreaFactor);
            dbCmd.Parameters.AddWithValue("@Detail_RegionCode", eInfo.RegionCode);
            dbCmd.Parameters.AddWithValue("@Detail_HospitalCode", eInfo.HospitalCode);
            dbCmd.Parameters.AddWithValue("@Detail_NewAmount", eInfo.NewAmount);
            dbCmd.Parameters.AddWithValue("@Detail_OldAmount", eInfo.OldAmount);
            dbCmd.Parameters.AddWithValue("@Detail_Type", eInfo.Type);
            dbCmd.Parameters.AddWithValue("@Detail_TakeCard", eInfo.TakeCard);
            dbCmd.Parameters.AddWithValue("@Detail_Status", eInfo.Status);
            dbCmd.Parameters.AddWithValue("@Detail_TotalMonth", eInfo.TotalMonth);
            dbCmd.Parameters.AddWithValue("@Detail_Description", eInfo.Description);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Detail_Delete(int DetailID, int _DocumentID)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Detail_ID", DetailID);
            dbCmd.Parameters.AddWithValue("@Document_ID", _DocumentID);
            try
            {
                dbConn.Open();
                if (dbCmd.ExecuteNonQuery() > 0)
                {
                    retVal = true;
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Detail_Exist(int _ID, string _SIBookNo, DateTime _FromDate, DateTime _ToDate, string _InsTypeCode, Decimal OldAmount, Decimal OldBasicFactor, Decimal OldPositionFactor, Decimal OldOverFactor, Decimal OldJobFactor, Decimal NewAmount, Decimal NewBasicFactor, Decimal NewPositionFactor, Decimal NewOverFactor, Decimal NewJobFactor)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Exist", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ID", _ID);
            dbCmd.Parameters.AddWithValue("@InsBookNo", _SIBookNo);
            dbCmd.Parameters.AddWithValue("@FromDate", _FromDate);
            dbCmd.Parameters.AddWithValue("@ToDate", _ToDate);
            dbCmd.Parameters.AddWithValue("@InsTypeCode", _InsTypeCode);

            dbCmd.Parameters.AddWithValue("@OldAmount", OldAmount);
            dbCmd.Parameters.AddWithValue("@OldBasicFactor", OldBasicFactor);
            dbCmd.Parameters.AddWithValue("@OldJobFactor", OldJobFactor);
            dbCmd.Parameters.AddWithValue("@OldOverFactor", OldOverFactor);
            dbCmd.Parameters.AddWithValue("@OldPositionFactor", OldPositionFactor);
            dbCmd.Parameters.AddWithValue("@NewAmount", NewAmount);
            dbCmd.Parameters.AddWithValue("@NewBasicFactor", NewBasicFactor);
            dbCmd.Parameters.AddWithValue("@NewJobFactor", NewJobFactor);
            dbCmd.Parameters.AddWithValue("@NewOverFactor", NewOverFactor);
            dbCmd.Parameters.AddWithValue("@NewPositionFactor", NewPositionFactor);

            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)
                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public bool Detail_CheckExistSIBookNo(string _SIBookNo, string _BranchCode)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_CheckExistSIBookNo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@InsBookNo", _SIBookNo);
            dbCmd.Parameters.AddWithValue("@BranchCode", _BranchCode);
            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)
                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03_Excel(int period, int month, int year, string BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Form03_Excel", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau02_Excel(int period, int month, int year, string BranchCode, bool _IsAll)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Form02_Excel_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@IsAll", _IsAll);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Detail_Mau02_TCT_Excel(int period, int month, int year, string BranchCode, bool _IsAll)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_Form02_TCT_Excel", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@IsAll", _IsAll);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public DataSet Detail_Mau03_TangMoi(int period, int month, int year, string BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_TangMoi", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03Giam(int period, int month, int year, string BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_Giam", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03DCLuong(int period, int month, int year, string BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_DCLuong", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03BoSungBHTN(int period, int month, int year, string BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_BoSungBHTN", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03BoSungBHYT(int period, int month, int year, string BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_BoSungBHYT", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_TongHopMau03(int period, int month, int year, string BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_TongHopMau03", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03CuoiKy(int period, int month, int year, string BranchCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_GetForForm03_TongHopCuoiKy", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        /*----------------------- Cac service test mau 03 chia theo HS, ML rieng---------------- */
        public DataSet Detail_Mau03_TangMoi_TEST(int period, int month, int year, string BranchCode,string TypeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_TangMoi_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@TypeCode", TypeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03Giam_TEST(int period, int month, int year, string BranchCode,string TypeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_Giam_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@TypeCode", TypeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03DCLuong_TEST(int period, int month, int year, string BranchCode,string TypeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_DCLuong_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@TypeCode", TypeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03BoSungBHTN_TEST(int period, int month, int year, string BranchCode,string TypeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_BoSungBHTN_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@TypeCode", TypeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03BoSungBHYT_TEST(int period, int month, int year, string BranchCode, string TypeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_Form03_BoSungBHYT_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@TypeCode", TypeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_TongHopMau03_TEST(int period, int month, int year, string BranchCode, string TypeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_TongHopMau03_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@TypeCode", TypeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_Mau03CuoiKy_TEST(int period, int month, int year, string BranchCode,string TypeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_GetForForm03_TongHopCuoiKy_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@TypeCode", TypeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }


        /*--------------------------END- mẫu 03 test------------------------------*/

        public DataSet Detail_TongHopMau02(int period, int month, int year, string BranchCode, bool _IsAll)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_TongHopMau02", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@IsAll", _IsAll);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_GetForForm02(int period, int _month, int _year, string BranchCode, bool _IsAll)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_GetForForm02", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", _month);
            dbCmd.Parameters.AddWithValue("@year", _year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@IsAll", _IsAll);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        /*Test form 02 */
        public DataSet Detail_TongHopMau02_TEST(int period, int month, int year, string BranchCode, bool _IsAll,string _TypeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_TongHopMau02_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", month);
            dbCmd.Parameters.AddWithValue("@year", year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@IsAll", _IsAll);
            dbCmd.Parameters.AddWithValue("@TypeCode", _TypeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public DataSet Detail_GetForForm02_TEST(int period, int _month, int _year, string BranchCode, bool _IsAll,string _TypeCode)
        {
            DataSet retVal = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_BaoHiem_GetForForm02_TEST", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@month", _month);
            dbCmd.Parameters.AddWithValue("@year", _year);
            dbCmd.Parameters.AddWithValue("@Period", period);
            dbCmd.Parameters.AddWithValue("@BranchCode", BranchCode);
            dbCmd.Parameters.AddWithValue("@IsAll", _IsAll);
            dbCmd.Parameters.AddWithValue("@TypeCode", _TypeCode);
            try
            {
                retVal = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        /*------------------*/

        public bool KiemTraGiaTriThangPhatSinh(string Detail_InsBookNo, DateTime Detail_ToDate)
        {
            bool retVal = false;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_KiemTraGiaTriThangPhatSinh", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Detail_InsBookNo", Detail_InsBookNo);
            dbCmd.Parameters.AddWithValue("@Detail_ToDate", Detail_ToDate);
            try
            {
                dbConn.Open();
                if (Convert.ToInt32(dbCmd.ExecuteScalar()) > 0)
                    retVal = true;
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public string GetLastestHSML(string Detail_InsBookNo, out DateTime ToDate)
        {
            string retVal = "";
            ToDate = DateTime.MinValue;
            SqlDataReader rdr = null;
            SqlConnection dbConn = new SqlConnection(Properties.Settings.Default.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("HRM_Detail_GetLastestHSML", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@Detail_InsBookNo", Detail_InsBookNo);
            try
            {
                dbConn.Open();
                rdr = dbCmd.ExecuteReader();
                while (rdr.Read())
                {
                    if( rdr["Detail_Type"]  != DBNull.Value)
                        retVal = (string)rdr["Detail_Type"];
                    if (rdr["Detail_ToDate"] != DBNull.Value)
                        ToDate = (DateTime)rdr["Detail_ToDate"];
                }
                
            }
            finally
            {
                // 3. close the reader
                if (rdr != null)
                {
                    rdr.Close();
                }

                // close the connection
                if (dbConn != null)
                {
                    dbConn.Close();
                }
            }	
            return retVal;
        }
    }
}
